<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>SQLite PHP tutorial</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, PHP, tutorial, database">
<meta name="description" content="This is SQLite PHP tutorial. In this tutorial, you will
learn the basics of database programming in SQLite and PHP language.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

<script type="text/javascript">
  (function() {
    var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;
    po.src = 'https://apis.google.com/js/plusone.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);
  })();
</script>

<script type="text/javascript">

  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-5536206-1']);
  _gaq.push(['_trackPageview']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();

</script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>


<h1>SQLite PHP tutorial</h1>

<p>
This is a PHP programming tutorial for the SQLite database. It covers the 
basics of SQLite programming with PHP language. There are two ways to code 
PHP scripts with SQLite library. We can use procedural functions or 
OOP objects and methods. In this tutorial, we use the classical procedural style. 
You might also want to check the <a href="/lang/php/">PHP tutorial</a> 
, <a href="/db/sqlite/">SQLite tutorial</a> or 
<a href="/db/sqliteperltutorial/">SQLite Perl tutorial</a> on ZetCode.
</p>


<!--I believe I found the problem. The webserver process needs write access to the directory that the 
sqlite DB file exists to create a journal file. It may also be possible to create the journal
 manually and give write access to that, but I have no ested that.-->

<!-- http://devzone.zend.com/article/760 -->
<!-- http://www.php.cn/php/ref.sqlite.html -->


<h2>Installation</h2>


<p>
To work with this tutorial, you must install several packages. The apache2, 
libapache2-mod-php5, php5-sqlite packages. sqlite command line tool is optional, but
recommended.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
The document root directory is a directory, where you place your
html and php files. It is a place, where the apache server looks
for the files, that make up the web site. 
</p>

<p>
The document root for apache2 server can be changed at
/etc/apache2/sites-available/default file. This is for Ubuntu. 
</p>

<pre class="code">
DocumentRoot /var/www/
</pre>

<p>
This is a portion of the above mention configuration file. The default
document root directory is /var/www.
</p>

<p>
We should also edit the php.ini file to turn the magic quotes off. 
Since PHP 5.3.0 they are off by default. On my system, I have 
currently PHP 5.2.6 so I had to edit the php.ini file. 
It is located at /etc/php5/apache2/php.ini on my system.
</p>

<p>
Magic Quotes is a process that automatically escapes incoming data to the PHP script. 
It's preferred to code with magic quotes off and to instead escape the data at runtime, as needed. 
We are going to use <code>sqlite_escape_string()</code> function 
to escape strings if necessary. 
</p>

<pre class="code">
; Magic quotes
;

; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = Off

; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(), etc.
magic_quotes_runtime = Off

; Use Sybase-style magic quotes (escape ' with '' instead of \').
magic_quotes_sybase = Off
</pre>

<p>
This is a portion of the php.ini file. Magic quotes are off. If you edited the file
while apache was running, you must restart the apache server. 
</p>

<p>
We are also going to create a directory, where we will have our sqlite database files.
In the document root directory, /var/www on my Ubuntu system, we create a directory called
db. 
</p>

<pre class="code">
$ pwd
/var/www
$ ls -ld db
drwxrwxrwx 2 root root 4096 2009-12-01 22:04 db
</pre>

<p>
A web server must have a write &amp; execute access to the directory. 
It is convenient to have a read access too. 
</p>

<pre class="code">
$ pwd
/var/www/db
$ ls -l test.db 
-rw-rw-rw- 1 root root 6144 2009-12-01 22:04 test.db
</pre>

<p>
Inside the db directory, we create a test.db file with read and
write access rights. 
</p>

<p>
SQLite database is called a zero configuration database. The only problems that
could arise are insufficient access rights.
</p>


<h2>First example</h2>

<p>
Our first example will test the version of the SQLite library and the version
of the PHP language. If it works, we have all installed correctly.
</p>

<p>
We create a simple PHP script and give it a name version.php. We place it
into the document root directory. It is /var/www on my system. Ensure, that
the apache server is running. 
</p>

<pre class="code">
$ /etc/init.d/apache2 status
 * Apache is running (pid 22965).
</pre>

<p>
We check, if the apache server is running. To start the server, we can use
the /etc/init.d/apache2 start command. 
</p>

<pre class="code">
&lt;?php
echo sqlite_libversion();
echo "&lt;br&gt;";
echo phpversion();
?&gt;
</pre>

<p>
Now we start the browser and locate to http://localhost/version.php
</p>

<p>
The PHP code shows 2.8.17 and 5.2.6-2ubuntu4.5 strings on my system. 
You should get something similar. 
</p>

<img src="/img/db/sqlitephp/version.png" alt="First example">
<div class="figure">Figure: First example</div>


<h2>Creating a table</h2>

<p>
In the following PHP code, we will create a database table. 
</p>

<pre class="code">
&lt;?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$stm = "CREATE TABLE Friends(Id integer PRIMARY KEY," . 
       "Name text UNIQUE NOT NULL, Sex text CHECK(Sex IN ('M', 'F')))";
$ok = sqlite_exec($dbhandle, $stm, $error);

if (!$ok)
   die("Cannot execute query. $error");

echo "Database Friends created successfully";

sqlite_close($dbhandle);
?&gt;
</pre>

<p>
Besides creating a database table, we do some error checking. 
</p>

<pre class="explanation">
$dbhandle = sqlite_open('db/test.db', 0666, $error);
</pre>

<p>
The <code>sqlite_open()</code> function opens a SQLite database.
The function has three parameters. The first parameter is the filename of
the database. According to the documentation, the second parameter is ignored currently.
The 0666 is the recommended value. If we cannot open the database, an error message
is put into the <b>$error</b> variable.
</p>

<pre class="explanation">
if (!$dbhandle) die ($error);
</pre>

<p>
The <code>sqlite_open()</code> function returns a database handle on
success or FALSE on error. The <code>die()</code> function outputs an error message
and terminates the script. 
</p>

<pre class="explanation">
$stm = "CREATE TABLE Friends(Id integer PRIMARY KEY," . 
    "Name text UNIQUE NOT NULL, Sex text CHECK(Sex IN ('M', 'F')))";
</pre>

<p>
The <b>$stm</b> variable holds the SQL statement to create a Friends database table. 
Note that there are two strings concatenated with the dot operator. 
</p>

<pre class="explanation">
$ok = sqlite_exec($dbhandle, $stm, $error);
</pre>

<p>
The <code>sqlite_exec()</code> executes a result-less statement 
against the database. The first parameter is the database handle, that we 
obtained with the <code>sqlite_open()</code> function. The second parameter 
is the statement, that we are about to execute. And the last parameter is the 
possible error message. This is usually due to a syntax error. The function 
returns TRUE for success or FALSE for failure.
</p>

<pre class="explanation">
if (!$ok)
   die("Cannot execute query. $error");
</pre>

<p>
We check for possible errors. There could be two types of errors. 
SQL syntax error or insufficient permissions.
</p>

<pre class="explanation">
echo "Database Friends created successfully";
</pre>

<p>
If all went OK, we print a message 'Database Friends created successfully'.
If there is some error, this message is not printed, because the <code>die()</code>
function terminates the execution of the PHP script.  
</p>

<pre class="explanation">
sqlite_close($dbhandle);
</pre>

<p>
We close the database handle. It is not necessary to do it explicitly. PHP language
does it automatically. But it is a good programming practice to do it. 
</p>


<h2>Inserting data</h2>

<p>
In the following example, we will insert some data into the Friends database. 
</p>

<pre class="code">
&lt;?php
   
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);
    
$stm1 = "INSERT INTO Friends VALUES(1,'Jane', 'F')";
$stm2 = "INSERT INTO Friends VALUES(2,'Thomas', 'M')";
$stm3 = "INSERT INTO Friends VALUES(3,'Franklin', 'M')";

$ok1 = sqlite_exec($dbhandle, $stm1);
if (!$ok1) die("Cannot execute statement.");

$ok2 = sqlite_exec($dbhandle, $stm2);
if (!$ok2) die("Cannot execute statement.");

$ok3 = sqlite_exec($dbhandle, $stm3);
if (!$ok3) die("Cannot execute statement.");

echo "Data inserted successfully";

sqlite_close($dbhandle);

?&gt;
</pre>

<p>
We insert some data. We don't retrieve any data. Therefore we use 
again the <code>sqlite_exec()</code> function. 
</p>

<pre class="explanation">
$stm1 = "INSERT INTO Friends VALUES(1,'Jane', 'F')";
$stm2 = "INSERT INTO Friends VALUES(2,'Thomas', 'M')";
$stm3 = "INSERT INTO Friends VALUES(3,'Franklin', 'M')";
</pre>

<p>
Here we have three statements that will insert three
rows into the Friends database. 
</p>


<pre class="explanation">
$ok1 = sqlite_exec($dbhandle, $stm1);
if (!$ok1) die("Cannot execute statement.");
</pre>

<p>
We execute the first statement. If something goes wrong, the 
script is terminated.  
</p>

<p>
What if we wanted to add a name like O'Neil? The single quote ' character
belongs to some unsafe characters. Using them could lead to 
problems. We must properly escape them. The single quote character is escaped by using another
single quote character. '' Note that it is easily confused with a double quote character. 
</p>

<pre class="code">
&lt;?php
   
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);

$name = "O'Neill";
$name_es = sqlite_escape_string($name);

$stm = "INSERT INTO Friends VALUES(4,'$name_es', 'M')";

$ok1 = sqlite_exec($dbhandle, $stm);
if (!$ok1) die("Cannot execute statement.");

echo "Data inserted successfully";

sqlite_close($dbhandle);

?&gt;
</pre>

<p>
In this code example, we add a fourth row to the Friends table. 
</p>

<pre class="explanation">
$name = "O'Neil";
</pre>

<p>
We have a name with a single quote character in it. 
</p>


<pre class="explanation">
$name_es = sqlite_escape_string($name);
</pre>

<p>
To escape the string, we use the <code>sqlite_escape_string()</code> function. 
The returned string is O''Neill. 
</p>

<pre class="explanation">
$stm = "INSERT INTO Friends VALUES(4,'$name_es', 'M')";
</pre>

<p>
We build the SQL statement with the <b>$name_es</b> variable. 
</p>

<pre class="code">
sqlite> SELECT * FROM Friends;
Id          Name        Sex       
----------  ----------  ----------
1           Jane        F         
2           Thomas      M         
3           Franklin    M         
4           O'Neil      M   
</pre>

<p>
We look with the sqlite command line tool, what we have in the table. 
All is OK.
</p>


<h2>Retrieving data</h2>

<p>
There are multiple ways, how we can retrieve data from 
a table. 
</p>

<pre class="code">
&lt;?php
   
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);
    
$query = "SELECT Name, Sex FROM Friends";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");

$row = sqlite_fetch_array($result, SQLITE_ASSOC); 
print_r($row);
echo "&lt;br&gt;";

sqlite_rewind($result);
$row = sqlite_fetch_array($result, SQLITE_NUM); 
print_r($row);
echo "&lt;br&gt;";

sqlite_rewind($result);
$row = sqlite_fetch_array($result, SQLITE_BOTH); 
print_r($row);
echo "&lt;br&gt;";

sqlite_close($dbhandle);

?&gt;
</pre>

<p>
To fetch data from the table, we can use the <code>sqlite_fetch_array()</code>.
</p>

<pre class="explanation">
$query = "SELECT Name, Sex FROM Friends";
$result = sqlite_query($dbhandle, $query);
</pre>

<p>
We build a SELECT query and execute the query with the <code>sqlite_query()</code> function.
The function returns a result set, e.g. all data from the query.
</p>

<p>
The <code>sqlite_fetch_array()</code> does two things. Moves the pointer to the
next row and returns that row from the result set. The row is is an array. 
We can control how the data is organized in the array, by using three result type flags.
<b>SQLITE_ASSOC</b>, <b>SQLITE_NUM</b>, <b>SQLITE_BOTH</b>. Using the first flag we will have an
associative array. Using the second one, we will have a numerical array. The third option is the default
option also. Using this flag, we will have both arrays with associative indexes and numerical indexes. 
The <code>print_r()</code> function returns a human readable representation of a variable. 
In our case, we can inspect what we have in an array. 
</p>

<pre class="explanation">
$row = sqlite_fetch_array($result, SQLITE_ASSOC); 
print_r($row);
echo "&lt;br&gt;";
</pre>

<p>
Here we fetch the first row from the result set. We use the <b>SQLITE_ASSOC</b> flag. Which means, we
can access data from the array using string indexes. The indexes are column names of the table. 
These are Name and Sex column names. Note that the SQL select statement did not include the id column. 
</p>

<pre class="explanation">
sqlite_rewind($result);
</pre>

<p>
The <code>sqlite_rewind()</code> function makes the pointer point to the first row
of the result set. We use this function because we want to compare three flags on the same row. 
For the sake of the clarity of the explanation.
</p>

<img src="/img/db/sqlitephp/retrieve.png" alt="Retrieving data">
<div class="figure">Figure: Retrieving data</div>

<p>
In the following example, we will traverse the data
using the associative indexes. 
</p>

<pre class="code">
&lt;?php
   
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);
    
$query = "SELECT Name, Sex FROM Friends";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");

while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
    echo $row['Name']  . " : " . $row['Sex'];
    echo "&lt;br&gt;";
}

sqlite_close($dbhandle);

?&gt;
</pre>

<p>
We traverse all data in our table. More specifically, four rows in the
Friends table. 
</p>

<pre class="explanation">
while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
    echo $row['Name']  . " : " . $row['Sex'];
    echo "&lt;br&gt;";
}
</pre>

<p>
We can use the <b>while</b> loop to go through all rows of the result set. 
The <b class="keyword">sqlite_fetch_array()</b> returns FALSE, if the next position
is beyond the final row and the loop stops. 
</p>


<pre class="explanation">
echo $row['Name']  . " : " . $row['Sex'];
</pre>

<p>
We get the data from the array using the string indexes. These are the
column names of the Friends table. 
</p>

<pre class="code">
while ($row = sqlite_fetch_array($result, SQLITE_NUM)) {
    echo $row[0]  . " : " . $row[1];
    echo "&lt;br&gt;";
}
</pre>

<p>
Same loop with the <b>SQLITE_NUM</b> flag. 
</p>


<h2>Columns &amp; rows</h2>

<p>
Next, we are going to count the number of rows and columns in our result set. 
</p>

<pre class="code">
&lt;?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);
    
$query = "SELECT * FROM Friends LIMIT 2";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");


$rows = sqlite_num_rows($result);
$cols = sqlite_num_fields($result);

echo "The result set has $rows rows and 
      $cols columns";

sqlite_close($dbhandle);

?&gt;
</pre>

<p>
The functions get the numbers from the result set. This means, that the number of 
rows and columns calculated depend on the SQL statement, that we use to obtain the
data from the database table. 
</p>

<pre class="explanation">
$query = "SELECT * FROM Friends LIMIT 2";
</pre>

<p>
Here we build the SQL query. We get all columns from the table. And we limit the
number of rows to 2. 
</p>

<pre class="explanation">
$rows = sqlite_num_rows($result);
$cols = sqlite_num_fields($result);
</pre>

<p>
The <code>sqlite_num_rows()</code> returns the number of rows in our 
result set. <code>sqlite_num_fields()</code> returns the number of columns/fields
from the result set. 
</p>

<p>
We get this string 'The result set has 2 rows and 3 columns'.
</p>

<p>
The next PHP script will display the data from the Friends table with
the names of the columns. 
</p>

<pre class="code">
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);
    
$query = "SELECT Name, Sex FROM Friends";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");

$rows = sqlite_num_rows($result);

$field1 = sqlite_field_name($result, 0);
$field2 = sqlite_field_name($result, 1);

echo "&lt;table style='font-size:12;font-family:verdana'&gt;";
echo "&lt;thead&gt;&lt;tr&gt;";
echo "&lt;th align='left'&gt;$field1&lt;/th&gt;";
echo "&lt;th align='left'&gt;$field2&lt;/th&gt;";
echo "&lt;/tr&gt;&lt;/thead&gt;";

for ($i = 0; $i &lt; $rows; $i++) {
    $row = sqlite_fetch_array($result, SQLITE_NUM); 
    echo "&lt;tr&gt;";
    echo "&lt;td&gt;$row[0]&lt;/td&gt;";
    echo "&lt;td&gt;$row[1]&lt;/td&gt;";
    echo "&lt;/tr&gt;";
}

echo "&lt;/table&gt;";

sqlite_close($dbhandle);

?>
</pre>


<pre class="explanation">
$field1 = sqlite_field_name($result, 0);
$field2 = sqlite_field_name($result, 1);
</pre>

<p>
The <code>sqlite_field_name()</code> returns the name of a particular field. 
Our SQL query returns two columns. The first function returns 'Name', the second 'Sex'. 
</p>

<pre class="explanation">
echo "&lt;thead&gt;&lt;tr&gt;";
echo "&lt;th align='left'&gt;$field1&lt;/th&gt;";
echo "&lt;th align='left'&gt;$field2&lt;/th&gt;";
echo "&lt;/tr&gt;&lt;/thead&gt;";
</pre>

<p>
We put the two column names into the html table header. 
</p>

<pre class="explanation">
for ($i = 0; $i &lt; $rows; $i++) {
    $row = sqlite_fetch_array($result, SQLITE_NUM); 
    echo "&lt;tr&gt;";
    echo "&lt;td&gt;$row[0]&lt;/td&gt;";
    echo "&lt;td&gt;$row[1]&lt;/td&gt;";
    echo "&lt;/tr&gt;";
}
</pre>

<p>
We use yet another way to retrieve data from the result set. We count the number
of rows. And use the <b>for</b> cycle to go through the data. 
</p>

<p>
The next PHP script will display column types of the Friends table. 
</p>

<pre class="code">
&lt;?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);
    
$cols = sqlite_fetch_column_types('Friends', $dbhandle, SQLITE_ASSOC);

foreach ($cols as $column => $type) {
    echo "Column name: $column Column type: $type";
    echo "&lt;br&gt;";
}

sqlite_close($dbhandle);
?&gt;
</pre>


<pre class="explanation">
$cols = sqlite_fetch_column_types('Friends', $dbhandle, SQLITE_ASSOC);
</pre>

<p>
The <code>sqlite_fetch_column_types()</code> function returns an 
array of column types from a particular table.
The table name is the first parameter of the function. 
</p>

<pre class="explanation">
foreach ($cols as $column => $type) {
    echo "Column name: $column Column type: $type";
    echo "&lt;br&gt;";
}
</pre>

<p>
We go through the array using the <b>foreach</b> keyword. 
</p>


<h2>Listing available tables</h2>

<p>
The next example will list all available tables from the current database. 
</p>

<pre class="code">
sqlite> .tables
Books    Cars     Friends
</pre>

<p>
Using the sqlite3 tool we list the available tables. 
</p>


<pre class="code">
&lt;?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);
    
$query = "SELECT name, sql FROM sqlite_master WHERE type='table'";
$result = sqlite_query($dbhandle, $query, SQLITE_NUM);
if (!$result) die("Cannot execute query.");

while (sqlite_has_more($result)) {
    $row = sqlite_fetch_array($result);
    echo "table: $row[0], sql: $row[1]";
    echo "&lt;br&gt;";
}

sqlite_close($dbhandle);

?&gt;
</pre>

<p>
We use the <b>sqlite_master</b> table to obtain the
list of tables for the database. 
</p>

<pre class="explanation">
$query = "SELECT name, sql FROM sqlite_master WHERE type='table'";
</pre>

<p>
This is the query. The name column of the <b>sqlite_master</b> table
gives us the table name. The sql column gives us the SQL used to create that table. 
</p>

<pre class="explanation">
while (sqlite_has_more($result)) {
    $row = sqlite_fetch_array($result);
    echo "table: $row[0], sql: $row[1]";
    echo "&lt;br&gt;";
}
</pre>

<p>
The <b>while</b> loop goes through the rows of the result set. We use a new function.
<code>sqlite_has_more()</code> returns TRUE if there are more rows available from the 
result set, or FALSE otherwise. 
</p>


<h2>Simple form example</h2>

<p>
In our last example, we will work with a simple html form. Submitting the form, we add
a new friend to the Friends table. 
</p>

<pre class="code">
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;SQLite PHP tutorial&lt;/title&gt;
&lt;/head&gt;
&lt;body style="font-size:12;font-family:verdana"&gt;

&lt;form action="add.php" method="post"&gt;

&lt;p&gt;
Name: &lt;input type="text" name="name"&gt;&lt;br&gt;
Male: &lt;input type="radio" value="M" name="gender"&gt;&lt;br&gt;
Female: &lt;input type="radio" value="F" name="gender"&gt;
&lt;/p&gt;

&lt;p&gt;
&lt;input type="submit"&gt;
&lt;/p&gt;

&lt;/form&gt;


&lt;/body&gt;
&lt;/html&gt;
</pre>

<p>
In our html form we have one text box and one radio box. We enter a name of a friend in 
the text box. The radio box determines the gender. The action property of the html form
points to the add.php script. This means, that upon submitting the form the add.php script
will run. 
</p>

<pre class="code">
&lt;?php

$gender = $_POST['gender'];
$name = $_POST['name'];

$name_es = sqlite_escape_string($name);

if (!empty($name)) {

   $dbhandle = sqlite_open('db/test.db', 0666, $error);

   if (!$dbhandle) die ($error);
   
   $stm = "INSERT INTO Friends(Name, Sex) VALUES('$name_es', '$gender')";
   $ok = sqlite_exec($dbhandle, $stm, $error);

   if (!$ok) die("Error: $error");  
   echo "Form submitted successfully";
}
?&gt;
</pre>

<p>
This is the add.php script. 
</p>

<pre class="explanation">
$gender = $_POST['gender'];
$name = $_POST['name'];
</pre>

<p>
We retrieve the data from the submitted form. 
</p>

<pre class="explanation">
$name_es = sqlite_escape_string($name);
</pre>

<p>
The data from the text box is potentionally unsafe; 'tainted'. 
We use the <code>sqlite_escape_string</code>. It escapes a string for use 
as a query parameter. This is common practice to avoid malicious sql injection attacks.
</p>

<pre class="explanation">
$stm = "INSERT INTO Friends(Name, Sex) VALUES('$name_es', '$gender')";
</pre>

<p>
Here we build the SQL statement. 
</p>

<pre class="explanation">
$ok = sqlite_exec($dbhandle, $stm, $error);
</pre>

<p>
Statement is executed. 
</p>

<img src="/img/db/sqlitephp/form.png" alt="Form example">
<div class="figure">Figure: Form example</div>
<br>

<div class="g-plusone"></div>

<p>
This was the SQLite PHP tutorial. We covered some basics of programming SQLite with PHP language. 
We used procedural style of code. 
</p>


<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified December 4, 2009  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>


